
package gs.eisenhower.swallet.db;

import gs.eisenhower.swallet.utils.common.AppController;
import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class WalletSqlHelper extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "WalletDB";
	private static final int DATABASE_VERSION = 1;
	
	// Common column name for all tables
	public static final String ID_COLUMN = "id";
	// Common column name for all table
	public static final String CATEGORY_COLUMN = "category";
	// Common column name for Wallet and Budget tables
	public static final String AMOUNT_COLUMN = "amount";
	// Common column name for Wallet and Category tables
    public static final String FINANCE_TYPE = "type";
	public static final String FINANCE_TYPE_EXP = "Expenditure";
	public static final String FINANCE_TYPE_EAR = "Earning";
	
	public static final String WALLET_TABLE_NAME = "Wallet";
	public static final String WALLET_DETAILS_COLUMN = "details";
	
	public static final String BUDGET_TABLE_NAME = "Budget";
	public static final String BUDGET_TRANSACTION_DATE = "transactionDate";
	
	public static final String CATEGORY_TABLE_NAME = "Category";
	
	public static final String CURRENCY_TABLE_NAME = "Currency";
	public static final String CURRENCY_NUMCODE_COLUMN = "numCode";
	public static final String CURRENCY_CHARCODE_COLUMN = "charCode";
	public static final String CURRENCY_NOMINAL_COLUMN = "nominal";
	public static final String CURRENCY_NAME_COLUMN = "name";
	public static final String CURRENCY_VALUE_COLUMN = "value";
	
	private final String WALLET_TABLE_CREATE_STATMENT = "CREATE TABLE "
			+ WALLET_TABLE_NAME + " (" + ID_COLUMN
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + AMOUNT_COLUMN
			+ " FLOAT NOT NULL, " + CATEGORY_COLUMN + " TEXT NOT NULL, "
			+ WALLET_DETAILS_COLUMN + " TEXT, " + FINANCE_TYPE
			+ " TEXT NOT NULL);";
	
	private final String BUDGET_TABLE_CREATE_STATMENT = "CREATE TABLE "
			+ BUDGET_TABLE_NAME + " (" + ID_COLUMN
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + AMOUNT_COLUMN
			+ " FLOAT NOT NULL, " + BUDGET_TRANSACTION_DATE
			+ " DATE NOT NULL);";
	
	private final String CATEGORY_TABLE_CREATE_STATMENT = "CREATE TABLE "
		    + CATEGORY_TABLE_NAME + " (" + ID_COLUMN + " INTEGER PRIMARY KEY AUTOINCREMENT, "
		    + CATEGORY_COLUMN + " TEXT NOT NULL, " + FINANCE_TYPE + " TEXT NOT NULL)";
	
	private final String CURRENCY_TABLE_CREATE_STATMENT = "CREATE TABLE "
			+ CURRENCY_TABLE_NAME + " (" + ID_COLUMN
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + CURRENCY_NUMCODE_COLUMN
			+ " TEXT, " + CURRENCY_CHARCODE_COLUMN + " TEXT, "
			+ CURRENCY_NOMINAL_COLUMN + " INTEGER, " + CURRENCY_NAME_COLUMN
			+ " TEXT, " + CURRENCY_VALUE_COLUMN + " FLOAT);";
	
	
    private static WalletSqlHelper INSTANCE;

    public static synchronized WalletSqlHelper getInstance() {
        if (INSTANCE == null) {
            INSTANCE = new WalletSqlHelper(AppController.getContext());
        }
        return INSTANCE;
    }

    private WalletSqlHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase database) {
		database.execSQL(WALLET_TABLE_CREATE_STATMENT);
		database.execSQL(CURRENCY_TABLE_CREATE_STATMENT);
		database.execSQL(CATEGORY_TABLE_CREATE_STATMENT);
		database.execSQL(BUDGET_TABLE_CREATE_STATMENT);
	
		// Add predefined categories
		ContentValues values = new ContentValues();
		values.put(FINANCE_TYPE, FINANCE_TYPE_EXP);
		values.put(CATEGORY_COLUMN, "Household");
		database.insert(CATEGORY_TABLE_NAME, null, values);
		values.put(CATEGORY_COLUMN, "Communication");
		database.insert(CATEGORY_TABLE_NAME, null, values);
		values.put(CATEGORY_COLUMN, "Clothes");
		database.insert(CATEGORY_TABLE_NAME, null, values);
		values.put(CATEGORY_COLUMN, "Transport");
		database.insert(CATEGORY_TABLE_NAME, null, values);
		values.put(CATEGORY_COLUMN, "Entertaiment");
		database.insert(CATEGORY_TABLE_NAME, null, values);
		
		values.put(FINANCE_TYPE, FINANCE_TYPE_EAR);
		values.put(CATEGORY_COLUMN, "Credit card");
		database.insert(CATEGORY_TABLE_NAME, null, values);
		values.put(CATEGORY_COLUMN, "Bank deposit");
		database.insert(CATEGORY_TABLE_NAME, null, values);
		values.put(CATEGORY_COLUMN, "Cash");
		database.insert(CATEGORY_TABLE_NAME, null, values);
		values.put(CATEGORY_COLUMN, "Savings");
		database.insert(CATEGORY_TABLE_NAME, null, values);		
	}

	@Override
	public void onUpgrade(SQLiteDatabase database, int arg1, int arg2) {
		database.execSQL("DROP TABLE IF EXISTS " + WALLET_TABLE_NAME);
		database.execSQL("DROP TABLE IF EXISTS " + CURRENCY_TABLE_NAME);
		database.execSQL("DROP TABLE IF EXISTS " + BUDGET_TABLE_NAME);
		database.execSQL("DROP TABLE IF EXISTS " + CATEGORY_TABLE_NAME);
	    onCreate(database);
	}
}
